﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Text.RegularExpressions;

namespace ClassLibrary1
{
    public static class Conversion
    {

        public  static XmlDocument SQL_XML_Convert(string sql_to_convert){
            return null;
        }

        public static string SQL_Commands_To_XML(string sql_to_convert)
        {
            XmlDocument xdCmdList= new XmlDocument();
            xdCmdList.Load(Path.Combine(Environment.CurrentDirectory,"sql-commands.xml"));

            sql_to_convert=remove_comments(sql_to_convert);

            Regex white_space;

            foreach (XmlNode xCommand in xdCmdList.SelectNodes("//command"))
            {
                white_space = new Regex(@"\s+(" + xCommand.InnerText + @")\s+", RegexOptions.IgnoreCase);
                sql_to_convert = white_space.Replace(sql_to_convert, match_command);
            }

            sql_to_convert = "<SQL>\n" + sql_to_convert + "\n</SQL>";
            /*
            Regex paramXML = new Regex(@"<SELECT/>\s*(.+)\s*<\bFROM\b/>|<SELECT/>\s*(.+)\s*<\bINTO\b/>", RegexOptions.Singleline);
            sql_to_convert = paramXML.Replace(sql_to_convert, delegate(Match param) {
            
                string cols = "";
                string param2 = param.Value.Replace("<SELECT/>", "").Replace("<FROM/>", "");
                foreach(string col in param2.Split(',')){
                    cols = cols + " <COLUMN> " + col + " </COLUMN> ,"; 
                }
                cols = " <SELECT/><COLUMNS>" + cols.TrimEnd(',') + "</COLUMNS><FROM/> ";
                return(cols); 
            });
             */
            return (sql_to_convert);
       }
        
        public static string remove_comments(string sql_to_clean)
        {
            Regex remove_comment = new Regex(@"(--.+\n)|(/[*][\S\s]+[*]/)");
            return (remove_comment.Replace(sql_to_clean, ""));
        }
        
        public static string match_command(Match command){
                    return (" <" + command.Value.ToUpper().Trim() + "/> ");
       }

        public static string Open_File_To_String(string file_to_open)
        {
            FileStream fsI=File.Open(file_to_open,FileMode.Open);
            byte[] bI=new byte[fsI.Length];
            fsI.Read(bI,0,(int)fsI.Length);
            Encoding eI = Encoding.ASCII;
            string sI=eI.GetString(bI);
            return (sI);
        }
    }
}
